import click
from config import Mysql


class Export(object):
    def __init__(self):

        self.select_sql_dict = {
            "list_page": "SELECT detail_url, detail_describe, label, region FROM list_page;",
            "regional_table_of_delicacies": "SELECT region, url FROM regional_table_of_delicacies;",
            "shop_info": "SELECT _rank, shop_name, another_name, shop_id, shop_tags, star_level, district, "
                         "taste, environment, serve, average_price, region, category, comment_number, "
                         "detailed_address, contact_way, business_hours, lng, lat FROM shop_info;",
            "shop_comment": "SELECT sku_number, shop_id, user_name, is_vip, user_level, star_level, "
                            "taste, environment, serve, average_price, favorite_dishes, thumbs_up, "
                            "comment_time, comment FROM shop_comment;"
        }

        self.insert_sql_dict = {
            "list_page": "INSERT INTO list_page(detail_url, detail_describe, label, region) "
                         "VALUES({!r}, {!r}, {!r}, {!r});",
            "regional_table_of_delicacies": "INSERT INTO regional_table_of_delicacies(region, url) VALUES({!r}, {!r});",
            "shop_info": "INSERT INTO shop_info(_rank, shop_name, another_name, shop_id, shop_tags, "
                         "star_level, district, taste, environment, serve, average_price, region, "
                         "category, comment_number, detailed_address, contact_way, business_hours, "
                         "lng, lat) VALUES({!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, "
                         "{!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r}, {!r});",
            "shop_comment": "INSERT INTO shop_comment(sku_number, shop_id, user_name, is_vip, user_level, "
                            "star_level, taste, environment, serve, average_price, favorite_dishes, "
                            "thumbs_up, comment_time, comment) VALUES ({!r}, {!r}, {!r}, {}, {}, {}, {!r}, "
                            "{!r}, {!r}, {}, {!r}, {}, {!r}, {!r});"
        }

        self.from_connection = None
        self.to_connection = None

    def export(self, from_connection, to_connection, key):
        self.from_connection, self.to_connection = from_connection, to_connection
        select_sql, insert_sql = self.select_sql_dict.get(key), self.insert_sql_dict.get(key)
        if (not select_sql) or (not insert_sql):
            raise Exception("输入数据表不存在！")

        self.export_table(key, select_sql, insert_sql)

    def export_table(self, table, select_sql, insert_sql):
        number = 0
        for args in self.from_connection.execute(select_sql).fetchall():
            try:
                self.to_connection.execute(insert_sql.format(*args))
            except Exception as e:
                pass

            number += 1
            if number % 1000 == 0:
                print(f"已导出{number}条数据")
        print(f"{table}表导出完成, 共有{number}条数据！")


@click.command()
@click.option("--from_db", default=0, help="请选择需要导出的数据库")
@click.option("--to_db", default=1, help="请选择需要导入的数据库")
@click.option("--table", default='all', help="请选择需要导出的数据表，默认全部导出")
def main(from_db, to_db, table):
    if table == "all":
        table_list = ["list_page", "regional_table_of_delicacies", "shop_info", "shop_comment"]
    else:
        table_list = [table]
    with Mysql('dp', from_db) as from_mysql:
        from_connection = from_mysql.engine.connect()
        with Mysql('dp', to_db) as to_mysql:
            to_connection = to_mysql.engine.connect()

            for table in table_list:
                print(f"开始导出{table}数据表")
                export = Export()
                export.export(from_connection, to_connection, table)
                print(f"{table}数据表导出完成")


if __name__ == '__main__':
    main()
